{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "###########调包\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from datetime import *\n",
    "import time\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "############数据文件文件路径\n",
    "train_dir = '../../contest/train/'\n",
    "B_dir = '../../contest/B榜/'\n",
    "train_pickle_dir = './pickle/train/'\n",
    "B_pickle_dir = './pickle/B/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_mode(series_x):\n",
    "    mode = (series_x.mode())[0]\n",
    "    return mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报1():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报信息表_T0 = pd.read_csv(os.path.join(data_dir,'XW_TAXDECLARE.csv'))    \n",
    "        else:\n",
    "            综合申报信息表_T0 = pd.read_csv(os.path.join(data_dir,'XW_TAXDECLARE_B.csv'))   \n",
    "        综合申报信息表_T0.columns = ['纳税人识别号','申报序号','所属日期起','所属日期止','征收项目名称','申报日期','申报期限','全部销售收入','应税销售收入','应纳税额','减免税额']\n",
    "\t\t\n",
    "        综合申报信息表_T0.sort_values(['纳税人识别号','征收项目名称','所属日期起','所属日期止','申报日期','申报序号'],inplace=True,ascending=True)\n",
    "\t\t\n",
    "        综合申报信息表_T0['全部销售收入'] = pow((综合申报信息表_T0['全部销售收入'])/3.12,3).round(2)\n",
    "        综合申报信息表_T0['应税销售收入'] = pow((综合申报信息表_T0['应税销售收入'])/3.12,3).round(2)\n",
    "        综合申报信息表_T0['应纳税额'] = pow((综合申报信息表_T0['应纳税额'])/3.12,3).round(2)\n",
    "        综合申报信息表_T0['减免税额'] = pow((综合申报信息表_T0['减免税额'])/3.12,3).round(2)\n",
    "\t\t\n",
    "        综合申报信息表_T0['所属日期起'] = 综合申报信息表_T0['所属日期起'].astype('str')\n",
    "        综合申报信息表_T0['所属日期起'] = 综合申报信息表_T0['所属日期起'].astype('datetime64[ns]')\n",
    "        综合申报信息表_T0['所属日期止'] = 综合申报信息表_T0['所属日期止'].astype('str')\n",
    "        综合申报信息表_T0['所属日期止'] = 综合申报信息表_T0['所属日期止'].astype('datetime64[ns]')\n",
    "        综合申报信息表_T0['申报日期'] = 综合申报信息表_T0['申报日期'].astype('str')\n",
    "        综合申报信息表_T0['申报日期'] = 综合申报信息表_T0['申报日期'].astype('datetime64[ns]')\n",
    "        综合申报信息表_T0['申报期限'] = 综合申报信息表_T0['申报期限'].astype('str')\n",
    "        综合申报信息表_T0['申报期限'] = 综合申报信息表_T0['申报期限'].astype('datetime64[ns]')\n",
    "\t\t\n",
    "        综合申报信息表_T0['所属日期起']=pd.to_datetime(综合申报信息表_T0['所属日期起'])+pd.DateOffset(days=11886)\n",
    "        综合申报信息表_T0['所属日期止']=pd.to_datetime(综合申报信息表_T0['所属日期止'])+pd.DateOffset(days=11886)\n",
    "        综合申报信息表_T0['申报日期']=pd.to_datetime(综合申报信息表_T0['申报日期'])+pd.DateOffset(days=11886)\n",
    "        综合申报信息表_T0['申报期限']=pd.to_datetime(综合申报信息表_T0['申报期限'])+pd.DateOffset(days=11886)\n",
    "\n",
    "        pickle.dump(综合申报信息表_T0, open(pickle_dir+'综合申报信息表_临时表.p', 'wb'))\n",
    "    return "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报2():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报信息表_T0 = pickle.load(open(pickle_dir+'综合申报信息表_临时表.p', 'rb'))\n",
    "        else:\n",
    "            综合申报信息表_T0 = pickle.load(open(pickle_dir+'综合申报信息表_临时表.p', 'rb'))\n",
    "\n",
    "        综合申报_tmp=综合申报信息表_T0.groupby(['纳税人识别号','征收项目名称','所属日期起','所属日期止']).agg({'申报日期':['max']})\n",
    "        综合申报_tmp.reset_index(inplace=True)\n",
    "        综合申报_tmp.columns = ['纳税人识别号','征收项目名称','所属日期起','所属日期止','申报日期']\n",
    "        \n",
    "        综合申报_T1=综合申报信息表_T0.merge(综合申报_tmp,on=['纳税人识别号','征收项目名称','所属日期起','所属日期止','申报日期'],how='inner')\n",
    "        \n",
    "        综合申报_tmp2=综合申报_T1.groupby(['纳税人识别号','征收项目名称','所属日期起','所属日期止']).agg({'申报序号':['max']})\n",
    "        综合申报_tmp2.reset_index(inplace=True)\n",
    "        综合申报_tmp2.columns = ['纳税人识别号','征收项目名称','所属日期起','所属日期止','申报序号']\n",
    "        \n",
    "        综合申报_T3=综合申报_T1.merge(综合申报_tmp2,on=['纳税人识别号','征收项目名称','所属日期起','所属日期止','申报序号'],how='inner')\n",
    "        \n",
    "        综合申报_T3['申报年起']=pd.DatetimeIndex(综合申报_T3['所属日期起']).year\n",
    "        综合申报_T3['申报月起']=pd.DatetimeIndex(综合申报_T3['所属日期起']).month\n",
    "        综合申报_T3['申报年止']=pd.DatetimeIndex(综合申报_T3['所属日期止']).year\n",
    "        综合申报_T3['申报月止']=pd.DatetimeIndex(综合申报_T3['所属日期止']).month\n",
    "\n",
    "        综合申报_T3['提前申报时间']= 综合申报_T3.apply(lambda x:(x['申报期限']-x['申报日期']).days, axis=1)\n",
    "        综合申报_T3['要求申报时间']= 综合申报_T3.apply(lambda x:(x['申报期限']-x['所属日期止']).days, axis=1)\n",
    "        综合申报_T3['晚申报标识']=np.where((综合申报_T3['提前申报时间'] < 0),1,0)\n",
    "        综合申报_T3['早申报标识']=np.where(((综合申报_T3['提前申报时间'] -综合申报_T3['提前申报时间']) > 0),1,0)\n",
    "\n",
    "        pickle.dump(综合申报_T3, open(pickle_dir+'综合申报.p', 'wb'))\n",
    "    return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报3():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'综合申报.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'综合申报.p', 'rb'))\n",
    "\n",
    "        综合申报_T0.drop(综合申报_T0[~(综合申报_T0.征收项目名称.isin(['增值税','企业所得税']))].index, inplace=True)\n",
    "\n",
    "        综合申报_T0.drop(['提前申报时间','要求申报时间','晚申报标识','早申报标识'],axis=1,inplace=True)\n",
    "\n",
    "        综合申报_T1= 综合申报_T0.loc[综合申报_T0['申报年起'] >=2018]\n",
    "        综合申报_T1= 综合申报_T1.loc[综合申报_T1['申报年起'] <=2020]\n",
    "        综合申报_T1= 综合申报_T1.loc[综合申报_T1['申报年起']==综合申报_T1['申报年止']]\n",
    "\n",
    "        所得税_T0= 综合申报_T1.loc[综合申报_T1['征收项目名称'] == '企业所得税']\n",
    "\n",
    "        所得税_tmp=所得税_T0.groupby(['纳税人识别号','所属日期止']).agg({'申报日期':['max']})\n",
    "        所得税_tmp.reset_index(inplace=True)\n",
    "        所得税_tmp.columns = ['纳税人识别号','所属日期止','申报日期']\n",
    "\n",
    "        所得税_T1=所得税_T0.merge(所得税_tmp,on=['纳税人识别号','所属日期止','申报日期'],how='inner')\n",
    "        \n",
    "        所得税_tmp2=所得税_T1.groupby(['纳税人识别号','所属日期止']).agg({'申报序号':['max']})\n",
    "        所得税_tmp2.reset_index(inplace=True)\n",
    "        所得税_tmp2.columns = ['纳税人识别号','所属日期止','申报序号']\n",
    "        \n",
    "        所得税_T2=所得税_T1.merge(所得税_tmp2,on=['纳税人识别号','所属日期止','申报序号'],how='inner')\n",
    "\n",
    "        所得税_tmp3=所得税_T2.groupby(['纳税人识别号','申报年起']).agg({'申报日期':['max']})\n",
    "        所得税_tmp3.reset_index(inplace=True)\n",
    "        所得税_tmp3.columns = ['纳税人识别号','申报年起','申报日期']\n",
    "\n",
    "        所得税_T3=所得税_T2.merge(所得税_tmp3,on=['纳税人识别号','申报年起','申报日期'],how='inner')\n",
    "\n",
    "        所得税_tmp4=所得税_T3.groupby(['纳税人识别号','申报年起']).agg({'所属日期止':['max']})\n",
    "        所得税_tmp4.reset_index(inplace=True)\n",
    "        所得税_tmp4.columns = ['纳税人识别号','申报年起','所属日期止']\n",
    "\t\t\n",
    "        所得税_T4=所得税_T3.merge(所得税_tmp4,on=['纳税人识别号','申报年起','所属日期止'],how='inner')\n",
    "        所得税_T4.drop(['申报序号','所属日期起','所属日期止','征收项目名称','申报日期'\\\n",
    "\t\t,'申报期限','申报月起','申报年止','申报月止'],axis=1,inplace=True)\n",
    "        所得税_T4.columns = ['纳税人识别号','所得税_年收入','所得税_应税收入','所得税_应纳税额','所得税_减免税额','申报年起']\n",
    "\t\t\n",
    "\n",
    "        增值税_T0= 综合申报_T1.loc[综合申报_T1['征收项目名称'] == '增值税']\n",
    "        增值税_T0['申报期窗口']= 增值税_T0.apply(lambda x:(x['所属日期止']-x['所属日期起']).days, axis=1)\n",
    "\t\t\n",
    "        增值税_按年汇总_T1=增值税_T0.groupby(['纳税人识别号','申报年起']).agg({'全部销售收入':['sum'],'应税销售收入':['sum']\\\n",
    "\t\t,'应纳税额':['sum'],'减免税额':['sum'],'申报期窗口':[get_mode,'count']})\n",
    "        增值税_按年汇总_T1.reset_index(inplace=True)\n",
    "        增值税_按年汇总_T1.columns = ['纳税人识别号','申报年起','增值税_年收入','增值税_应税收入','增值税_应纳税额'\\\n",
    "\t\t,'增值税_减免税额','增值税_申报期窗口','增值税_期数']\n",
    "\n",
    "        综合纳税特征_T0=增值税_按年汇总_T1.merge(所得税_T4,on=['纳税人识别号','申报年起'],how='outer')\n",
    "        综合纳税特征_T0.sort_values(['纳税人识别号','申报年起'],inplace=True,ascending=True)\n",
    "\n",
    "        pickle.dump(综合纳税特征_T0, open(pickle_dir+'增长纳税按年临时表.p', 'wb'))\n",
    "    return "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报补充():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'综合申报信息表_临时表.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'综合申报信息表_临时表.p', 'rb'))\n",
    "\t\t\t\n",
    "        综合申报_T1=综合申报_T0.groupby(['纳税人识别号','征收项目名称']).agg({'所属日期起':['max','min']})\n",
    "        综合申报_T1.reset_index(inplace=True)\n",
    "        综合申报_T1.columns = ['纳税人识别号','征收项目名称','所属日期起_max','所属日期起_min']\n",
    "\t\t\n",
    "        综合申报_T2=综合申报_T1.groupby(['纳税人识别号']).agg({'所属日期起_min':['min']})\n",
    "        综合申报_T2.reset_index(inplace=True)\n",
    "        综合申报_T2.columns = ['纳税人识别号','最早申报日']\n",
    "        综合申报_T2['最大日期']='2021-03-31'\n",
    "        综合申报_T2['最大日期'] = 综合申报_T2['最大日期'].astype('datetime64[ns]')\n",
    "        综合申报_T2['最早申报日'] = 综合申报_T2['最早申报日'].astype('datetime64[ns]')\n",
    "        综合申报_T2['企业建立日']= 综合申报_T2.apply(lambda x:(x['最大日期']-x['最早申报日']).days, axis=1)\n",
    "        综合申报_T2.drop(['最大日期','最早申报日'],axis=1,inplace=True)\n",
    "\t\t\n",
    "        综合申报_T1.drop(综合申报_T1[~(综合申报_T1.征收项目名称.isin(['增值税','企业所得税']))].index, inplace=True)\n",
    "        综合申报_T3=综合申报_T1.groupby(['纳税人识别号']).agg({'所属日期起_max':['max']})\n",
    "        综合申报_T3.reset_index(inplace=True)\n",
    "        综合申报_T3.columns = ['纳税人识别号','最近申报日']\n",
    "\t\t\n",
    "        综合申报_T3['最大日期']='2021-03-31'\n",
    "        综合申报_T3['最大日期'] = 综合申报_T3['最大日期'].astype('datetime64[ns]')\n",
    "        综合申报_T3['最近申报日'] = 综合申报_T3['最近申报日'].astype('datetime64[ns]')\n",
    "        综合申报_T3['企业最近申报日']= 综合申报_T3.apply(lambda x:(x['最大日期']-x['最近申报日']).days, axis=1)\n",
    "        综合申报_T3['企业最近申报月']= 综合申报_T3['企业最近申报日']//30\n",
    "        综合申报_T3.drop(['企业最近申报日','最大日期','最近申报日'],axis=1,inplace=True)\n",
    "\t\t\n",
    "       \n",
    "        综合申报_T4=综合申报_T0.drop(综合申报_T0[~(综合申报_T0.征收项目名称.isin(['印花税']))].index)\n",
    "        综合申报_T4['申报年起']=pd.DatetimeIndex(综合申报_T4['所属日期起']).year\n",
    "        综合申报_T4=综合申报_T4.loc[综合申报_T4['申报年起']>=2017]\n",
    "        综合申报_T5=综合申报_T4.groupby(['纳税人识别号']).agg({'应纳税额':['count','sum'],'减免税额':['sum']})\n",
    "        综合申报_T5.reset_index(inplace=True)\n",
    "        综合申报_T5.columns = ['纳税人识别号','印花税申报次数','印花税应纳税额','印花税减免税额']\n",
    "\t\t\n",
    "        综合申报_T6=综合申报_T0.drop(综合申报_T0[~(综合申报_T0.征收项目名称.isin(['城市维护建设税','地方教育附加']))].index)\n",
    "        综合申报_T6['申报年起']=pd.DatetimeIndex(综合申报_T6['所属日期起']).year\n",
    "        综合申报_T6=综合申报_T6.loc[综合申报_T6['申报年起']>=2017]\n",
    "\t\t\n",
    "        综合申报_T7=综合申报_T6.groupby(['纳税人识别号','征收项目名称']).agg({'应税销售收入':['sum'],'应纳税额':['sum']})\n",
    "        综合申报_T7.reset_index(inplace=True)\n",
    "        综合申报_T7.columns = ['纳税人识别号','征收项目名称','应税销售收入','应纳税额']\n",
    "\t\t\n",
    "        综合申报_T7['税率']=(100*综合申报_T7['应纳税额']/综合申报_T7['应税销售收入']).round(0)\n",
    "        综合申报_T7['税率'] =np.where((综合申报_T7['税率']>7),np.nan,综合申报_T7['税率'])\n",
    "\t\t\n",
    "        综合申报_T8=pd.pivot_table(综合申报_T7,index=['纳税人识别号'],columns='征收项目名称')\n",
    "        综合申报_T8.fillna(0,inplace=True)\n",
    "        result=[]\n",
    "        for col in 综合申报_T8.columns.values:\n",
    "            tmp = []\n",
    "            tmp=str(col[1])+'_'+col[0]\n",
    "            result.append(tmp)\n",
    "\n",
    "        综合申报_T8.columns = result\n",
    "        综合申报_T8.reset_index(inplace=True)\n",
    "\n",
    "        综合申报_T8['地方教育附加_税率'] =np.where((综合申报_T8['地方教育附加_税率']>2),2,综合申报_T8['地方教育附加_税率'])\n",
    "        综合申报_T8['地方教育附加_税率'] = 综合申报_T8['地方教育附加_税率'].astype('category')\n",
    "        综合申报_T8['城市维护建设税_税率'] = 综合申报_T8['城市维护建设税_税率'].astype('category')\n",
    "\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET_B.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        综合申报补充=目标客户列表.merge(综合申报_T2,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充=综合申报补充.merge(综合申报_T3,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充=综合申报补充.merge(综合申报_T5,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充=综合申报补充.merge(综合申报_T8,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(综合申报补充, open(pickle_dir+'综合申报补充.p', 'wb'))\n",
    "        res.append(综合申报补充)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报特征():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'增长纳税按年临时表.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'增长纳税按年临时表.p', 'rb'))\n",
    "        综合申报_T0['合并年收入']=np.where(综合申报_T0['增值税_年收入'].isnull()==True,综合申报_T0['所得税_年收入'],综合申报_T0['增值税_年收入'])\n",
    "        综合申报_T0['合并年收入']=np.where(((综合申报_T0['增值税_年收入'].isnull()==False) &(综合申报_T0['所得税_年收入'].isnull()==False)),综合申报_T0[['增值税_年收入','所得税_年收入']].max(axis=1),综合申报_T0['合并年收入'])\n",
    "\n",
    "        综合申报_T0['合并收入年比'] =np.where((综合申报_T0.groupby(['纳税人识别号'])['合并年收入'].shift(1)<100000),np.nan,(综合申报_T0.groupby(['纳税人识别号'])['合并年收入'].diff(1)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['合并年收入'].shift(1))).round(2))\n",
    "\t\n",
    "        综合申报_T0['合并上年收入']=综合申报_T0.groupby(['纳税人识别号'])['合并年收入'].shift(1)\t\n",
    "        综合申报_T0['合并收入上年比']=综合申报_T0.groupby(['纳税人识别号'])['合并收入年比'].shift(1)\n",
    "        综合申报_T0['合并收入年差']=综合申报_T0.groupby(['纳税人识别号'])['合并年收入'].diff(1)\n",
    "        综合申报_T0['合并收入上年差']=综合申报_T0.groupby(['纳税人识别号'])['合并收入年差'].shift(1)\n",
    "\n",
    "        综合申报_T0['增值税_年收入年比'] =np.where((综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入'].shift(1)<100000),np.nan,(综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入'].diff(1)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入'].shift(1))).round(2))\n",
    "        综合申报_T0['所得税_年收入年比'] =np.where((综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入'].shift(1)<100000),np.nan,(综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入'].diff(1)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入'].shift(1))).round(2))\t\n",
    "\t\t\n",
    "        综合申报_T0['增值税_年收入差']=综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入'].diff(1)\n",
    "        综合申报_T0['所得税_年收入差']=综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入'].diff(1)\n",
    "        综合申报_T0['增值税_上年收入差']=综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入差'].shift(1)\n",
    "        综合申报_T0['所得税_上年收入差']=综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入差'].shift(1)\n",
    "\n",
    "        综合申报_T0['增值税_年收入上年比']=综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入年比'].shift(1)\n",
    "        综合申报_T0['所得税_年收入上年比']=综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入年比'].shift(1)\n",
    "\t\t\n",
    "        综合申报_T0['增值税_税率']=(100*综合申报_T0['增值税_应纳税额']/综合申报_T0['增值税_应税收入']).round(0)\n",
    "\n",
    "        综合申报_T0['增值税减免比']=(100*综合申报_T0['增值税_减免税额']/综合申报_T0['增值税_应纳税额']).round(0)\n",
    "        综合申报_T0['所得税减免比']=(100*综合申报_T0['所得税_减免税额']/综合申报_T0['所得税_应纳税额']).round(0)\n",
    "\n",
    "        综合申报_T0['增值税_税率'] =np.where((综合申报_T0['增值税_税率']>17),np.nan,综合申报_T0['增值税_税率'])\n",
    "        综合申报_T0['增值税_税率'] =np.where((综合申报_T0['增值税_税率']>=10),10,综合申报_T0['增值税_税率'])\n",
    "        综合申报_T0['增值税_税率'] =np.where((综合申报_T0['增值税_税率']<0),np.nan,综合申报_T0['增值税_税率'])\n",
    "\n",
    "        综合申报_T0['增值税_税率'].fillna(综合申报_T0.groupby(['纳税人识别号'])['增值税_税率'].shift(1),inplace=True)\n",
    "        综合申报_T0['所得税减免比'].fillna(综合申报_T0.groupby(['纳税人识别号'])['所得税减免比'].shift(1),inplace=True)\n",
    "        综合申报_T0['增值税减免比'].fillna(综合申报_T0.groupby(['纳税人识别号'])['增值税减免比'].shift(1),inplace=True)\n",
    "\n",
    "        综合申报_T0['增值税_应纳税额年比'] =np.where((综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额'].shift(1)<1000),np.nan,(综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额'].diff(1)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额'].shift(1))).round(2))\n",
    "        综合申报_T0['所得税_应纳税额年比'] =np.where((综合申报_T0.groupby(['纳税人识别号'])['所得税_应纳税额'].shift(1)<1000),np.nan,(综合申报_T0.groupby(['纳税人识别号'])['所得税_应纳税额'].diff(1)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['所得税_应纳税额'].shift(1))).round(2))\n",
    "\n",
    "        综合申报_T0['增值税_应纳税额年差']=综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额'].diff(1)\n",
    "        综合申报_T0['所得税_应纳税额年差']=综合申报_T0.groupby(['纳税人识别号'])['所得税_应纳税额'].diff(1)\n",
    "        综合申报_T0['增值税_上年收入差']=综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额年差'].shift(1)\n",
    "        综合申报_T0['所得税_上年收入差']=综合申报_T0.groupby(['纳税人识别号'])['所得税_应纳税额年差'].shift(1)\n",
    "\n",
    "        综合申报_T0['增值税_应纳税额上年比']=综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额年比'].shift(1)\n",
    "        综合申报_T0['所得税_应纳税额上年比']=综合申报_T0.groupby(['纳税人识别号'])['所得税_应纳税额年比'].shift(1)\n",
    "\t\t\n",
    "        综合申报_T1=综合申报_T0.groupby(['纳税人识别号']).agg({'合并年收入':['sum','min'],'合并收入年差':['sum','min','max']\\\n",
    "\t\t,'增值税_年收入':['sum'],'所得税_年收入':['sum']\\\n",
    "\t\t,'增值税_年收入年比':['min','mean'],'所得税_年收入年比':['min','mean']\\\n",
    "\t\t,'增值税_年收入差':['sum','min'],'所得税_年收入差':['sum','min']\\\n",
    "\t\t,'增值税_应纳税额':['sum','min'],'所得税_应纳税额':['sum','min']\\\n",
    "\t\t,'增值税_减免税额':['sum'],'所得税_减免税额':['sum']\\\n",
    "\t\t,'增值税_应纳税额年差':['sum','min'],'所得税_应纳税额年差':['sum','min']\\\n",
    "\t\t,'增值税_应纳税额年比':['min','mean'],'所得税_应纳税额年比':['min','mean']\\\n",
    "\t\t,'增值税_税率':['last'],'增值税_期数':['last','std'],'增值税_申报期窗口':['last'],'申报年起':['last']})\n",
    "        result=[]\n",
    "        for col in 综合申报_T1.columns.values:\n",
    "            tmp= col[0]+'_'+col[1]\n",
    "            result.append(tmp)\n",
    "        综合申报_T1.columns = result\n",
    "        综合申报_T1.reset_index(inplace=True)\n",
    "\t\t\n",
    "        综合申报_T2= 综合申报_T0.loc[综合申报_T0['申报年起'] ==2020]\n",
    "        综合申报_T2.drop(['申报年起','增值税_申报期窗口','增值税_期数','增值税_税率','增值税减免比','所得税减免比'],axis=1,inplace=True)\n",
    "        result=[]\n",
    "        for col in 综合申报_T2.columns.values:\n",
    "            tmp= col+'_2020'\n",
    "            result.append(tmp)\n",
    "        综合申报_T2.columns = result\n",
    "        综合申报_T2 = 综合申报_T2.rename(columns={'纳税人识别号_2020':'纳税人识别号'})\n",
    "\t\t\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(train_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET_B.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        综合申报_T3=目标客户列表.merge(综合申报_T1,on=['纳税人识别号'],how='left')\n",
    "        综合申报_T3=综合申报_T3.merge(综合申报_T2,on=['纳税人识别号'],how='left')\n",
    "        综合申报_T3.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        综合申报_T3['增值税_税率_last'] = 综合申报_T3['增值税_税率_last'].astype('category')\n",
    "        综合申报_T3['申报年起_last'] = 综合申报_T3['申报年起_last'].astype('category')\n",
    "\n",
    "        pickle.dump(综合申报_T3, open(pickle_dir+'申报特征.p', 'wb'))\n",
    "        res.append(综合申报_T3)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报补充2():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'综合申报.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'综合申报.p', 'rb'))\n",
    "\n",
    "        综合申报_T0.drop(['晚申报标识','早申报标识'],axis=1,inplace=True)\n",
    "        \n",
    "        综合申报_T1= 综合申报_T0.loc[综合申报_T0['申报年起'] >=2018]\n",
    "        综合申报_T1.sort_values(['纳税人识别号','征收项目名称','申报日期'],inplace=True,ascending=True)\n",
    "\n",
    "        综合申报_T1['上期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(1)\n",
    "        综合申报_T1['下期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(-1)\n",
    "\n",
    "        综合申报_T2= 综合申报_T1.loc[~((综合申报_T1['上期申报年起'] ==综合申报_T1['下期申报年起']) \\\n",
    "\t\t& (综合申报_T1['申报年起']<综合申报_T1['上期申报年起']))]\n",
    "\n",
    "        综合申报_T2['上期所属日期止']=综合申报_T2.groupby(['纳税人识别号','征收项目名称'])['所属日期止'].shift(1)\n",
    "        综合申报_T2['申报间隔']= 综合申报_T2.apply(lambda x:(x['所属日期起']-x['上期所属日期止']).days, axis=1)\n",
    "\n",
    "        pickle.dump(综合申报_T2, open(pickle_dir+'综合补充_临时表.p', 'wb'))\n",
    "\n",
    "    return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报补充3():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'综合补充_临时表.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'综合补充_临时表.p', 'rb'))\n",
    "\n",
    "        综合申报_T1= 综合申报_T0.loc[综合申报_T0['申报间隔'] >-300]\n",
    "        综合申报_T1= 综合申报_T1.loc[综合申报_T1['申报间隔'] !=1]\n",
    "\n",
    "        综合申报_T2=综合申报_T1.groupby(['纳税人识别号','征收项目名称','申报年起']).agg({'申报间隔':['max','count','sum']})\n",
    "        综合申报_T2.reset_index(inplace=True)\n",
    "        综合申报_T2.columns = ['纳税人识别号','征收项目名称','申报年起','最长漏报天','漏报次数','漏报天_sum']\n",
    "\n",
    "        综合申报_T3= 综合申报_T2.drop(综合申报_T2[~(综合申报_T2.征收项目名称.isin(['企业所得税','增值税']))].index)\n",
    "\n",
    "        综合申报_T4=综合申报_T3.groupby(['纳税人识别号']).agg({'最长漏报天':['max'],'漏报次数':['sum','mean'],'漏报天_sum':['sum']})\n",
    "        综合申报_T4.reset_index(inplace=True)\n",
    "        综合申报_T4.columns = ['纳税人识别号','最长漏报天','漏报次数','漏报次数_mean','漏报天_sum']\n",
    "\n",
    "        综合申报_T5=综合申报_T0.drop(综合申报_T0[~(综合申报_T0.征收项目名称.isin(['企业所得税']))].index)\n",
    "\n",
    "        综合申报_T6=综合申报_T5.groupby(['纳税人识别号']).agg({'提前申报时间':['mean','std'],'要求申报时间':['max']})\n",
    "        综合申报_T6.reset_index(inplace=True)\n",
    "        综合申报_T6.columns = ['纳税人识别号','提前申报时间_mean','提前申报时间_std','要求申报时间_max']\n",
    "\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET_B.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        综合申报补充=目标客户列表.merge(综合申报_T4,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充=综合申报补充.merge(综合申报_T6,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(综合申报补充, open(pickle_dir+'综合申报补充2.p', 'wb'))\n",
    "        res.append(综合申报补充)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报补充4():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'综合申报.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(pickle_dir+'综合申报.p', 'rb'))\n",
    "\t\t\t\n",
    "        综合申报_T3=综合申报_T0.drop(综合申报_T0[~(综合申报_T0.征收项目名称.isin(['增值税','企业所得税']))].index)\n",
    "        综合申报_T3= 综合申报_T3.loc[综合申报_T0['申报年起'] >=2018]\n",
    "        无销售收入_T0= 综合申报_T3.loc[综合申报_T3['全部销售收入'] ==0]\n",
    "        无应税收入_T0= 综合申报_T3.loc[综合申报_T3['应税销售收入'] ==0]\n",
    "        无应纳税额_T0= 综合申报_T3.loc[综合申报_T3['应纳税额'] ==0]\n",
    "\t\t\n",
    "        无销售收入_T1=无销售收入_T0.groupby(['纳税人识别号']).agg({'全部销售收入':['count']})\n",
    "        无销售收入_T1.reset_index(inplace=True)\n",
    "        无销售收入_T1.columns = ['纳税人识别号','三年内无销售收入次数']\n",
    "\t\t\n",
    "        无应税收入_T1=无应税收入_T0.groupby(['纳税人识别号']).agg({'全部销售收入':['count']})\n",
    "        无应税收入_T1.reset_index(inplace=True)\n",
    "        无应税收入_T1.columns = ['纳税人识别号','三年内无应税收入次数']\n",
    "\t\t\n",
    "        无应纳税额_T1=无应纳税额_T0.groupby(['纳税人识别号']).agg({'全部销售收入':['count']})\n",
    "        无应纳税额_T1.reset_index(inplace=True)\n",
    "        无应纳税额_T1.columns = ['纳税人识别号','三年内无应纳税额次数']\n",
    "\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET_B.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        综合申报补充3=目标客户列表.merge(无销售收入_T1,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充3=综合申报补充3.merge(无应税收入_T1,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充3=综合申报补充3.merge(无应纳税额_T1,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充3.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(综合申报补充3, open(pickle_dir+'综合申报补充3.p', 'wb'))\n",
    "        res.append(综合申报补充3)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "加工综合申报1()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "加工综合申报2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-6-b552da55f489>:48: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  增值税_T0['申报期窗口']= 增值税_T0.apply(lambda x:(x['所属日期止']-x['所属日期起']).days, axis=1)\n",
      "<ipython-input-6-b552da55f489>:48: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  增值税_T0['申报期窗口']= 增值税_T0.apply(lambda x:(x['所属日期止']-x['所属日期起']).days, axis=1)\n"
     ]
    }
   ],
   "source": [
    "加工综合申报3()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "综合申报补充_训练集,综合申报补充_测试集=加工综合申报补充()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50000, 12)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "综合申报补充_训练集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5939, 12)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "综合申报补充_测试集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-8-9c5f6e8465fd>:75: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T2.drop(['申报年起','增值税_申报期窗口','增值税_期数','增值税_税率','增值税减免比','所得税减免比'],axis=1,inplace=True)\n",
      "<ipython-input-8-9c5f6e8465fd>:75: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T2.drop(['申报年起','增值税_申报期窗口','增值税_期数','增值税_税率','增值税减免比','所得税减免比'],axis=1,inplace=True)\n"
     ]
    }
   ],
   "source": [
    "申报特征_训练集,申报特征_测试集=加工综合申报特征()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50000, 63)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "申报特征_训练集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5939, 63)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "申报特征_测试集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-9-bb5357ebe1e5>:12: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1.sort_values(['纳税人识别号','征收项目名称','申报日期'],inplace=True,ascending=True)\n",
      "<ipython-input-9-bb5357ebe1e5>:14: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1['上期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(1)\n",
      "<ipython-input-9-bb5357ebe1e5>:15: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1['下期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(-1)\n",
      "<ipython-input-9-bb5357ebe1e5>:20: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T2['上期所属日期止']=综合申报_T2.groupby(['纳税人识别号','征收项目名称'])['所属日期止'].shift(1)\n",
      "<ipython-input-9-bb5357ebe1e5>:21: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T2['申报间隔']= 综合申报_T2.apply(lambda x:(x['所属日期起']-x['上期所属日期止']).days, axis=1)\n",
      "<ipython-input-9-bb5357ebe1e5>:12: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1.sort_values(['纳税人识别号','征收项目名称','申报日期'],inplace=True,ascending=True)\n",
      "<ipython-input-9-bb5357ebe1e5>:14: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1['上期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(1)\n",
      "<ipython-input-9-bb5357ebe1e5>:15: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1['下期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(-1)\n",
      "<ipython-input-9-bb5357ebe1e5>:20: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T2['上期所属日期止']=综合申报_T2.groupby(['纳税人识别号','征收项目名称'])['所属日期止'].shift(1)\n",
      "<ipython-input-9-bb5357ebe1e5>:21: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T2['申报间隔']= 综合申报_T2.apply(lambda x:(x['所属日期起']-x['上期所属日期止']).days, axis=1)\n"
     ]
    }
   ],
   "source": [
    "加工综合申报补充2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "申报补充2_训练集,申报补充2_测试集=加工综合申报补充3()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50000, 8)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "申报补充2_训练集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5939, 8)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "申报补充2_测试集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "申报补充3_训练集,申报补充3_测试集=加工综合申报补充4()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50000, 4)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "申报补充3_训练集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5939, 4)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "申报补充3_测试集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#税务重构版本-修改最新日期/尝试排序/尝试脱敏规则改变脱敏规则\n",
    "def 加工税务特征():\n",
    "    res = [] \n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            税务表0 = pickle.load(open(pickle_dir+'综合申报信息表_临时表.p', 'rb'))\n",
    "        else:\n",
    "            税务表0 = pickle.load(open(pickle_dir+'综合申报信息表_临时表.p', 'rb'))        \n",
    "                           \n",
    "        #固定所属日期最大值\n",
    "        税务表0['最新日期']=max(税务表0['所属日期止'])\n",
    "        \n",
    "        #最早纳税情况\n",
    "        最早纳税表0=税务表0.loc[税务表0['应纳税额']>0].copy()\n",
    "        最早纳税表=最早纳税表0.groupby(['纳税人识别号']).agg({'申报日期':['min']})\n",
    "        最早纳税表.reset_index(inplace=True)\n",
    "        最早纳税表.columns = ['纳税人识别号','最早申报日期']\n",
    "\n",
    "        最早纳税表['最早申报日期距今天数'] = pd.to_datetime(税务表0['最新日期'])-pd.to_datetime(最早纳税表['最早申报日期'])\n",
    "        最早纳税表['最早申报日期距今天数'] = 最早纳税表['最早申报日期距今天数'] .astype('timedelta64[D]')\n",
    "        最早纳税表.drop(['最早申报日期'],axis=1,inplace=True)\n",
    "\n",
    "        \n",
    "        ##获取任意时间差表\n",
    "        最新纳税表T0=税务表0.groupby(['纳税人识别号']).agg({'所属日期止':['max']})\n",
    "        最新纳税表T0.reset_index(inplace=True)\n",
    "        最新纳税表T0.columns = ['纳税人识别号','所属日期止最新']\n",
    "        最新纳税表T0=最新纳税表T0.merge(税务表0,on=['纳税人识别号'],how='left')\n",
    "        最新纳税表T0['时间差'] = pd.to_datetime(税务表0['最新日期'])-pd.to_datetime(最新纳税表T0['所属日期止'])\n",
    "        最新纳税表T0['时间差'] = 最新纳税表T0['时间差'].astype('timedelta64[D]')\n",
    "        \n",
    "         ##获取近12个月数据\n",
    "        最新纳税表0=最新纳税表T0[最新纳税表T0['时间差']<=365]\n",
    "        最新纳税表01=最新纳税表0[最新纳税表0['征收项目名称']=='增值税']\n",
    "        最新纳税表02=最新纳税表0[最新纳税表0['征收项目名称']=='企业所得税']\n",
    "        最新纳税表0_1=最新纳税表01.groupby(['纳税人识别号']).agg({'减免税额':['sum']})\n",
    "        最新纳税表0_1.reset_index(inplace=True)\n",
    "        最新纳税表0_1.columns = ['纳税人识别号','减免汇总增']\n",
    "        最新纳税表0_2=最新纳税表02.groupby(['纳税人识别号']).agg({'减免税额':['sum']})\n",
    "        最新纳税表0_2.reset_index(inplace=True)\n",
    "        最新纳税表0_2.columns = ['纳税人识别号','减免汇总所']\n",
    "        最新纳税表汇总=最新纳税表0_1.merge(最新纳税表0_2,on=['纳税人识别号'],how='left')\n",
    "        最新纳税表汇总['企业近3个月减免税额']= 最新纳税表汇总['减免汇总增']+最新纳税表汇总['减免汇总所']\n",
    "        最新纳税表汇总['企业近3个月减免税额']=最新纳税表汇总['企业近3个月减免税额']\n",
    "        \n",
    "        \n",
    "        最新纳税表0_4=最新纳税表02.groupby(['纳税人识别号']).agg({'全部销售收入':['sum'],'应纳税额':['sum']})\n",
    "        最新纳税表0_4.reset_index(inplace=True)\n",
    "        最新纳税表0_4.columns = ['纳税人识别号','近3个月销售收入','近3个月所得税']\n",
    "        最新纳税表0_4['近3个月经营费用']=最新纳税表0_4['近3个月销售收入']-最新纳税表0_4['近3个月所得税']\n",
    "        \n",
    "        最新纳税表0_5=税务表0.loc[税务表0['征收项目名称']=='印花税'].copy()\n",
    "        最新纳税表0_5=最新纳税表0_5.groupby(['纳税人识别号']).agg({'申报日期':['max'],'申报期限':['max']})\n",
    "        最新纳税表0_5.reset_index(inplace=True)\n",
    "        最新纳税表0_5.columns = ['纳税人识别号','申报日期最新','申报期限最新']\n",
    "        最新纳税表0_5['申报范围']=最新纳税表0_5['申报期限最新']-最新纳税表0_5['申报日期最新']\n",
    "        最新纳税表0_5['申报范围']=  最新纳税表0_5['申报范围'].astype('timedelta64[D]')\n",
    "      \n",
    "        最新纳税表0_3=最新纳税表01.groupby(['纳税人识别号']).agg({'全部销售收入':['sum']})\n",
    "        最新纳税表0_3.reset_index(inplace=True)\n",
    "        最新纳税表0_3.columns = ['纳税人识别号','企业近3个月的全部销售收入']\n",
    "\n",
    "        最新纳税表汇总=最新纳税表汇总.merge(最新纳税表0_3,on=['纳税人识别号'],how='left')\n",
    "        最新纳税表汇总.drop(['减免汇总所'],axis=1,inplace=True)\n",
    "        最新纳税表汇总.drop(['减免汇总增'],axis=1,inplace=True)\n",
    "        \n",
    "        最新纳税表汇总=最新纳税表汇总.merge(最新纳税表0_4,on=['纳税人识别号'],how='left')\n",
    "        最新纳税表汇总.drop(['近3个月销售收入'],axis=1,inplace=True)\n",
    "        最新纳税表汇总.drop(['近3个月所得税'],axis=1,inplace=True)\n",
    "        \n",
    "        最新纳税表汇总=最新纳税表汇总.merge(最新纳税表0_5,on=['纳税人识别号'],how='left')\n",
    "        最新纳税表汇总.drop(['申报日期最新','申报期限最新'],axis=1,inplace=True)\n",
    "\n",
    "# 近12个月收入同比增幅：最近12个月销售收入汇总-最近24个月至最近12个月的销售收入汇总/最近24个月至最近12个月的销售收入汇总\n",
    "\n",
    "# 具体处理方法：\n",
    "# 1、根据该客户最新的“所属日期止”获取最近12个月数据，以及最近24个月至最近12个月的数据；\n",
    "# 2、税（费）种类为增值税；\n",
    "# 3、对销售收入进行汇总。\n",
    "\n",
    "        近6个月纳税表0=最新纳税表T0[最新纳税表T0['时间差']<=180]\n",
    "        近6个月纳税表001=近6个月纳税表0[近6个月纳税表0['征收项目名称']=='增值税']\n",
    "        近6个月纳税表01=近6个月纳税表001.groupby(['纳税人识别号']).agg({'全部销售收入':['sum']})\n",
    "        近6个月纳税表01.reset_index(inplace=True)\n",
    "        近6个月纳税表01.columns = ['纳税人识别号','企业近6个月的全部销售收入']\n",
    "        \n",
    "        \n",
    "        近12个月纳税表0=最新纳税表T0[最新纳税表T0['时间差']<=365]\n",
    "        近12个月纳税表001=近12个月纳税表0[近12个月纳税表0['征收项目名称']=='增值税']\n",
    "        近12个月纳税表01=近12个月纳税表001.groupby(['纳税人识别号']).agg({'全部销售收入':['sum'],'应纳税额':['sum'],'应税销售收入':['sum']})\n",
    "        近12个月纳税表01.reset_index(inplace=True)\n",
    "        近12个月纳税表01.columns = ['纳税人识别号','企业近12个月的全部销售收入','应纳税额汇总','应税销售收入汇总']\n",
    "        \n",
    "        \n",
    "        近12个月纳税表01.drop(['应纳税额汇总','应税销售收入汇总'],axis=1,inplace=True)\n",
    "  \n",
    "# 根据综合申报信息中，税（费）种类为“增值税”、申报日期为最近12个月内、全部销售收入为0且应纳税总额为0的记录数是否大于等于5，如果是则不能准入。\n",
    "# 具体处理方式： \n",
    "# 1、“所属日期止”往前推12个月，将每个月数据中的销售收入、应纳税额分别汇总；\n",
    "# 2、税（费）种类为增值税；\n",
    "# 3、某月份数据销售收入总额为0且应纳税总额为0，即说明报税为0。\n",
    " \n",
    "        中间加工=近6个月纳税表01.merge(近12个月纳税表01,on=['纳税人识别号'],how='left')\n",
    "        中间加工['汇总差']=中间加工['企业近12个月的全部销售收入']-中间加工['企业近6个月的全部销售收入']\n",
    "        中间加工['汇总差'].fillna(0,inplace=True)\n",
    "        \n",
    "        中间加工1=中间加工[中间加工['汇总差']!=0]\n",
    "        \n",
    "        中间加工1['半年同比增幅']=(中间加工1['企业近6个月的全部销售收入']-中间加工1['汇总差'])/中间加工1['汇总差']\n",
    "        中间加工1.drop(['企业近12个月的全部销售收入','企业近6个月的全部销售收入','汇总差'],axis=1,inplace=True)\n",
    "     \n",
    "        #暴力处理金额\n",
    "        税务表_0=税务表0.loc[(税务表0['征收项目名称']=='企业所得税')].copy()\n",
    "        税务表_0=税务表_0.groupby(['纳税人识别号']).agg({'全部销售收入':['sum','max','min','count','std','mean'],'应纳税额':['sum','max','min','count','std','mean']})\n",
    "        税务表_0.reset_index(inplace=True)\n",
    "        税务表_0.columns = ['纳税人识别号','所全部销售收入汇总','所全部销售收入最大','所全部销售收入最小','所全部销售收入count','所全部销售收入标准差','所全部销售收入平均','所应纳税额汇总','所应纳税额最大','所应纳税额最小','所应纳税额count','所应纳税额标准差','所应纳税额平均']\n",
    "        \n",
    "        税务表_1=税务表0.loc[(税务表0['征收项目名称']=='增值税')].copy()\n",
    "        税务表_1=税务表_1.groupby(['纳税人识别号']).agg({'应纳税额':['sum','max','min','count','std','mean'],'全部销售收入':['sum','max','min','count','std'],'申报日期':['count'],'应税销售收入':['sum']})\n",
    "        税务表_1.reset_index(inplace=True)\n",
    "        税务表_1.columns = ['纳税人识别号','应纳税额汇总','应纳税额最大','应纳税额最小','应纳税额count','应纳税额标准差','应纳税额平均','全部销售收入汇总','全部销售收入最大','全部销售收入最小','全部销售收入count','全部销售收入标准差','申报期数','应税销售收入汇总']\n",
    "\n",
    "        税务表_2=税务表_1.copy()\n",
    "        税务表_2['税率']=税务表_2['应纳税额汇总']/(税务表_2['全部销售收入汇总']+1)\n",
    "        税务表_2=税务表_2.groupby(['纳税人识别号']).agg({'税率':['mean']})\n",
    "        税务表_2.reset_index(inplace=True)\n",
    "        税务表_2.columns = ['纳税人识别号','税率']               \n",
    "       \n",
    "          \n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET_B.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "        \n",
    "\n",
    "        税务特征=目标客户列表.merge(最新纳税表汇总,on=['纳税人识别号'],how='left')\n",
    "        税务特征=税务特征.merge(最早纳税表,on=['纳税人识别号'],how='left')\n",
    "        税务特征=税务特征.merge(税务表_0,on=['纳税人识别号'],how='left')\n",
    "        税务特征=税务特征.merge(税务表_1,on=['纳税人识别号'],how='left')\n",
    "        税务特征=税务特征.merge(中间加工1,on=['纳税人识别号'],how='left')\n",
    "        税务特征=税务特征.merge(税务表_2,on=['纳税人识别号'],how='left')\n",
    "\n",
    "        税务特征.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    " \n",
    "        税务特征.drop_duplicates(subset=None,keep='first',inplace=True)#整行去重\n",
    "        税务特征.duplicated().sum()\n",
    "        pickle.dump(税务特征, open(pickle_dir+'Z税务特征.p', 'wb'))\n",
    "\n",
    "        res.append(税务特征)\n",
    "        \n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-28-46829b4b43a0>:109: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  中间加工1['半年同比增幅']=(中间加工1['企业近6个月的全部销售收入']-中间加工1['汇总差'])/中间加工1['汇总差']\n",
      "<ipython-input-28-46829b4b43a0>:110: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  中间加工1.drop(['企业近12个月的全部销售收入','企业近6个月的全部销售收入','汇总差'],axis=1,inplace=True)\n",
      "<ipython-input-28-46829b4b43a0>:109: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  中间加工1['半年同比增幅']=(中间加工1['企业近6个月的全部销售收入']-中间加工1['汇总差'])/中间加工1['汇总差']\n",
      "<ipython-input-28-46829b4b43a0>:110: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  中间加工1.drop(['企业近12个月的全部销售收入','企业近6个月的全部销售收入','汇总差'],axis=1,inplace=True)\n"
     ]
    }
   ],
   "source": [
    "税务特征_训练集,税务特征_训练集= 加工税务特征()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5939, 33)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "税务特征_训练集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5939, 33)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "税务特征_训练集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
